package com.bstek.code.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.bstek.bdf2.core.CoreJdbcDao;
import com.bstek.code.model.ColumnInfo;
import com.bstek.code.model.TableInfo;
import com.bstek.code.util.CodeUtil;

/**
 * 查询数据库信息
 * 
 * @company www.bstek.com
 * @author hans
 * @createDate 2017年6月15日
 */
@Repository(DBInfoDao.BEAN_ID)
public class DBInfoDao extends CoreJdbcDao {
	public static final String BEAN_ID = "DBINFODAO";

	/**
	 * 查询符合条件的table，包括列信息
	 * 
	 * @author hans
	 * @createDate 2017年6月15日
	 * @param prefix
	 *            表名关键字
	 * @return
	 * @throws Exception
	 */
	public List<TableInfo> getTablesByPrefix(String prefix) throws Exception {
		List<TableInfo> tables = new ArrayList<TableInfo>();
		StringBuffer sqlBuf = new StringBuffer(
				"SELECT A.TABLE_NAME,B.COMMENTS FROM USER_TABLES A LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME WHERE 1=1 ");
		List<Object> paraList = new ArrayList<Object>();
		if (StringUtils.isNotBlank(prefix)) {
			sqlBuf.append(" AND A.TABLE_NAME LIKE ? ");
			paraList.add("%" + prefix.toUpperCase() + "%");
		}
		sqlBuf.append(" ORDER BY A.TABLE_NAME");
		List<TableInfo> list = this.getJdbcTemplate().query(sqlBuf.toString(), paraList.toArray(),
				new TableInfoRowMapper());
		for (TableInfo tab : list) {
			tab.setCols(this.getFields(tab));
			List<ColumnInfo> u = this.getConstraintsInfo(tab.getName(), "U"); // 唯一约束
			tab.setUniqueKey(u);// 设置唯一键
			List<ColumnInfo> p = this.getConstraintsInfo(tab.getName(), "P");// 联合主键
			tab.setuPrimaryKey(p);// 主键
			tables.add(tab);
		}
		return tables;
	}

	/**
	 * 查询tables，不包括列信息
	 * 
	 * @param prefix
	 *            表名关键字
	 * @return
	 * @throws Exception
	 */
	public List<TableInfo> getTables(String prefix) throws Exception {
		StringBuffer sqlBuf = new StringBuffer(
				"SELECT A.TABLE_NAME,B.COMMENTS FROM USER_TABLES A LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME WHERE 1=1 ");
		List<Object> paraList = new ArrayList<Object>();
		if (StringUtils.isNotBlank(prefix)) {
			sqlBuf.append(" AND A.TABLE_NAME LIKE ? ");
			paraList.add("%" + prefix.toUpperCase() + "%");
		}
		sqlBuf.append(" ORDER BY A.TABLE_NAME");
		return this.getJdbcTemplate().query(sqlBuf.toString(), paraList.toArray(), new TableInfoRowMapper());
	}

	/**
	 * 查询符合条件的table，包括列信息
	 * 
	 * @author hans
	 * @createDate 2017年6月15日
	 * @param tab
	 *            以逗号分割的表名
	 * @return
	 * @throws Exception
	 */
	public List<TableInfo> getTablesByNames(String tabStr) throws Exception {
		List<TableInfo> tables = new ArrayList<TableInfo>();
		String[] t = tabStr.split(",");
		String tabs = "";
		for (int i = 0; i < t.length; i++) {
			tabs += "'" + t[i].toUpperCase() + "'";
			if (i < t.length - 1) {
				tabs += ",";
			}
		}
		String sql = "SELECT A.TABLE_NAME,B.COMMENTS FROM USER_TABLES A LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME WHERE A.TABLE_NAME in ("
				+ tabs.toUpperCase() + ") ORDER BY A.TABLE_NAME";
		List<TableInfo> list = this.getJdbcTemplate().query(sql, new TableInfoRowMapper());
		for (TableInfo tab : list) {
			tab.setCols(this.getFields(tab));
			List<ColumnInfo> u = this.getConstraintsInfo(tab.getName(), "U"); // 唯一约束
			tab.setUniqueKey(u);// 设置唯一键
			List<ColumnInfo> p = this.getConstraintsInfo(tab.getName(), "P");// 联合主键
			tab.setuPrimaryKey(p);// 主键
			tables.add(tab);
		}
		return tables;
	}

	/**
	 * 查询列信息
	 * 
	 * @author hans
	 * @createDate 2017年6月16日
	 * 
	 * @param TableInfo
	 *            tab表名
	 * @return
	 * @throws Exception
	 */
	public List<ColumnInfo> getFields(TableInfo tab) throws Exception {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT A.COLUMN_NAME,");
		sqlBuf.append(" A.DATA_TYPE,");
		sqlBuf.append(" TO_NUMBER(A.DATA_LENGTH) DATA_LENGTH,");
		sqlBuf.append(" TO_NUMBER(NVL(A.DATA_PRECISION, 0)) DATA_PRECISION,");
		sqlBuf.append(" TO_NUMBER(NVL(A.DATA_SCALE, 0)) DATA_SCALE,");
		sqlBuf.append(" A.TABLE_NAME,");
		sqlBuf.append(" A.NULLABLE,");
		sqlBuf.append(" B.COMMENTS,");
		sqlBuf.append(" C.CONSTRAINT_NAME,");
		sqlBuf.append(" C.CONSTRAINT_TYPE");
		sqlBuf.append(" FROM USER_TAB_COLUMNS A");
		sqlBuf.append(" LEFT JOIN USER_COL_COMMENTS B");
		sqlBuf.append(" ON A.TABLE_NAME = B.TABLE_NAME");
		sqlBuf.append(" AND A.COLUMN_NAME = B.COLUMN_NAME");
		sqlBuf.append(" LEFT JOIN (SELECT E.CONSTRAINT_NAME, E.CONSTRAINT_TYPE, F.COLUMN_NAME");
		sqlBuf.append(" FROM USER_CONSTRAINTS E");
		sqlBuf.append(" LEFT JOIN USER_CONS_COLUMNS F");
		sqlBuf.append(" ON E.CONSTRAINT_NAME = F.CONSTRAINT_NAME");
		sqlBuf.append(" WHERE E.TABLE_NAME = ?");
		sqlBuf.append(" AND CONSTRAINT_TYPE IN ('P', 'U')) C");// 只保留：主键、唯一键
		sqlBuf.append(" ON A. COLUMN_NAME = C.COLUMN_NAME");
		sqlBuf.append(" WHERE A.TABLE_NAME = ?");
		sqlBuf.append(" order by  A.COLUMN_ID");
		return this.getJdbcTemplate().query(sqlBuf.toString(), new Object[] { tab.getName(), tab.getName() },
				new ColumnInfoRowMapper());
	}

	/**
	 * 查询表的约束信息
	 * 
	 * @author hans
	 * @createDate 2016年5月30日
	 * 
	 * @param tableName
	 * @param type
	 *            约束类型：<br>
	 *            C check on a table; <br>
	 *            P primary key; <br>
	 *            R referential AKA foreign key; <br>
	 *            U unique key; <br>
	 *            V check option on a view; <br>
	 * @return
	 * @throws Exception
	 */
	public List<ColumnInfo> getConstraintsInfo(String tableName, String type) throws Exception {
		String sql = "select a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,b.COLUMN_NAME from user_constraints a left join user_cons_columns b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME where a.TABLE_NAME =? and CONSTRAINT_TYPE=?";
		return this.getJdbcTemplate().query(sql, new Object[] { tableName.toUpperCase(), type.toUpperCase() },
				new ConstraintInfoRowMapper());
	}

}

class TableInfoRowMapper implements RowMapper<TableInfo> {
	public TableInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
		TableInfo table = new TableInfo();
		table.setName(rs.getString("TABLE_NAME"));
		table.setLabel(rs.getString("COMMENTS") == null ? rs.getString("TABLE_NAME") : rs.getString("COMMENTS"));
		table.setjName(CodeUtil.getEntityName(rs.getString("TABLE_NAME")));
		return table;
	}
}

class ColumnInfoRowMapper implements RowMapper<ColumnInfo> {
	public ColumnInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
		ColumnInfo col = new ColumnInfo();
		String tabName = rs.getString("TABLE_NAME");
		String columnName = rs.getString("COLUMN_NAME");
		col.setName(columnName);// 列名
		col.setjName(CodeUtil.getFieldName(columnName));// java 字段名
		int dataLength = rs.getInt("DATA_LENGTH");
		col.setLength(dataLength);// 字符长度
		int precision = rs.getInt("DATA_PRECISION");
		col.setPrecision(precision);// number, 总长度
		int scale = rs.getInt("DATA_SCALE");
		col.setScale(scale);// 小数位
		String dataType = rs.getString("DATA_TYPE");
		String fieldType = "String";// 默认是字符串类型
		if (dataType.startsWith("VARCHAR2") || dataType.startsWith("CHAR")) {
			fieldType = "String";
		} else if (dataType.startsWith("NUMBER")) {
			if (scale == 0) {
				fieldType = "Integer";
				// 超过Int的范围2147483648的整型是Long
				if (precision > 10) {
					fieldType = "Long";
				}
			} else {
				fieldType = "BigDecimal";
			}
		} else if (dataType.equalsIgnoreCase("FLOAT") || dataType.equalsIgnoreCase("DOUBLE")) {
			fieldType = "BigDecimal";
		} else if (dataType.equals("INT")) {
			fieldType = "Integer";
		} else if (dataType.equals("LONG")) {
			fieldType = "Long";
		} else if (dataType.equals("BLOB")) {
			fieldType = "String";
		} else if (dataType.equals("CLOB")) {
			fieldType = "String";
		} else if (dataType.startsWith("TIMESTAMP") || dataType.startsWith("DATE")) {
			fieldType = "Date";
		} else {
			throw new RuntimeException(tabName + "." + columnName + "的数据类型" + dataType + "暂不支持");
		}
		col.setDataType(dataType);// 类型
		col.setjDataType(fieldType);// 类型
		boolean nullable = rs.getString("NULLABLE").equalsIgnoreCase("Y") ? true : false;
		col.setNullable(nullable);// 是否为空
		col.setLabel(rs.getString("COMMENTS") == null ? columnName : rs.getString("COMMENTS").toString());// 说明文字
		col.setConstraintsName(
				rs.getString("CONSTRAINT_NAME") == null ? "" : rs.getString("CONSTRAINT_NAME").toString());// 约束名
		String constraintType = rs.getString("CONSTRAINT_TYPE") == null ? ""
				: rs.getString("CONSTRAINT_TYPE").toString();
		col.setConstraintsType(constraintType);
		// 拼装column字段
		String colStr = CodeUtil.getColStr(fieldType, nullable, columnName, dataLength, precision, scale);
		col.setColStr(colStr);
		return col;
	}
}

class ConstraintInfoRowMapper implements RowMapper<ColumnInfo> {
	public ColumnInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
		ColumnInfo cons = new ColumnInfo();
		cons.setConstraintsName(rs.getString("CONSTRAINT_NAME"));
		cons.setName(rs.getString("COLUMN_NAME"));
		cons.setConstraintsType(rs.getString("CONSTRAINT_TYPE"));

		return cons;
	}
}